
package com.compuvest.advert

import javax.servlet.ServletConfig

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.util.zip.ZipOutputStream
import java.util.zip.ZipEntry

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPConnectionClosedException;

import javax.naming.InitialContext

import com.compuvest.mail.GoogleEmail;

import groovy.sql.Sql

class MakeFile extends HttpServlet {

    def static PATH         = '/var/lighttpd/1.4/docroot/advert'
    def static IMG_URL      = 'http://img.compuvest.com/0'
    def static WEB_LINK     = 'http://www.compuvest.com/Search.jsp?Search='
    def static ITEM_LINK    = 'http://www.compuvest.com/Desc.jsp?iid='

    def sql
    def gmail

// <editor-fold defaultstate="collapsed" desc="init()">

    def void init(ServletConfig config) {
	super.init(config)

        // init sql
        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)

        sql.eachRow('select VERSION()') { row ->
            println ">>>>MakeFile() load version=${row[0]}"
        }
        sql.close()

        // init google email
        gmail = new GoogleEmail()

    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="processRequest()">

    def void processRequest(request, response) {

        def exec = request.getParameter("exec")
        if(exec) {

            def contnt = new StringBuilder()
            if(exec == 'marat') {
                contnt << makeCatchMarketing()

                ftp("catchmarketingftp.com", "compuvest@catchmarketingftp.com", "compuvest3141~", "marat.zip", "compuvest.zip")
            }
            else if(exec == 'amazon') {
                //contnt << initTempTables();
                contnt << doAmazon()
            }
            else if(exec == 'grabber') {
                //contnt << initTempTables();
                contnt << doPriceGrabber()
            }
            else if(exec == 'epy') {
                //contnt << initTempTables();
                contnt << doEpy()
            }
            else if(exec == 'allstock') {
                //contnt << initTempTables();
                contnt << doAmazon()
                //contnt << doPriceGrabber()
            }

            response.setContentType("text/html;charset=UTF-8")
            def out = response.getWriter()
            try {
                out.print(contnt as String)
            }
            finally {
                out.close()
            }
        }

    }
// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="makeMaratItems()">

    def static SELECT_MARAT = """
        SELECT it.accwareCatID accwareCatID,ad.mfrNumber mfrNumber,ad.mfrName mfrName,cs.froogle conds,ps.description pack,
        it.name itemName,it.stock_quantity qty,it.sell_price sellPrice,it.item_id itemID,
        CONCAT(IFNULL(gc.name,'Generic'),' > ',IFNULL(ct.name,'Generic')) catName,it.keywords keywords,ad.upc upc,ids.xml xml
        FROM sklad.items it
        LEFT JOIN sklad.itemsAdvert ad USING(item_id)
        LEFT JOIN sklad.itemsDesc ids USING(item_id)
        LEFT JOIN sklad.category ct USING(category_id)
        LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
        LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id 
        LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id 
        WHERE it.active='Y' """
        //AND it.accwareCatID IN ('452002361-00') LIMIT 1 """

    def makeCatchMarketing() {

        def parser = new XmlSlurper()

        def zip = new ZipOutputStream(new FileOutputStream("${PATH}/marat.zip"))
        zip.putNextEntry(new ZipEntry("compuvest.txt"))

        zip << "CompuVest P/N,Mnf. P/N,Mnf. Name,Condition,Package,Description,Quantity on Hand,Price, Image URL,Item URL,Category Name,Keywords,Marketing Description,More Marketing Description,Upc\r\n"

        def i = 0
        sql.eachRow(SELECT_MARAT)
        { row ->

            def desc = [], minfo, edesc
            try {
                edesc = parser.parseText(row.xml)
            }
            catch(ignore) {
                println "bad xml ${row.accwareCatID}"
                edesc = null
            }

            // select all attributes
            if(edesc) {
                i++

                edesc.MSPEC.S.M.each { m ->
                    desc << "${m.L.text()}:${m.D.text()}"
                }

                edesc.ESPEC.S.each { s ->
                    s.M.each { m ->
                        desc << "${m.L.text()}:${m.D.text()}"
                    }
                }

                edesc.SATTR.D.each { d ->
                    desc << "${d.@ATTR.text()}:${d.text()}"
                }

                minfo = edesc.MINFO.text()
                //println "desc=${desc.join('|').toString()}"

                zip <<
                    "${row.accwareCatID}," +
                    "${row.mfrNumber}," +
                    "${row.mfrName ? row.mfrName.replaceAll(/[^a-zA-Z0-9]+/,' ') : ''}," +
                    "${row.conds}," +
                    "${row.pack}," +
                    "${row.itemName ? row.itemName.replaceAll(/[^a-zA-Z0-9]+/,' ') : ''}," +
                    "${row.qty}," +
                    "${row.sellPrice}," +
                    "${IMG_URL}/${row.accwareCatID}.jpg," +
                    "${ITEM_LINK}${row.itemID}," +
                    "${row.catName ? row.catName.replaceAll(/[^a-zA-Z0-9\>]+/,' ') : ''}," +
                    "${row.keywords ? row.keywords.replaceAll(/[^a-zA-Z0-9]+/,' ') : ''}," +
                    "${desc ? desc.join('|').toString().replaceAll(/[^a-zA-Z0-9\|]+/,' ') : ''}," +
                    "${minfo ? minfo.replaceAll(/[^a-zA-Z0-9]+/,' ') : ''}," +
                    "${row.upc}\r\n"
            }
        }
        sql.close()

        zip.closeEntry()
        zip.close()

        return "Count: ${i}"
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="doAmazon()">
    def doAmazon() {

        def res = [:]
        def contnt = new StringBuilder("-------------Amazon------------\n")

        res << makeAmazon()
        if(res.result == 'ok') {
            contnt << "Make file: items: ${res.count} ${res.time}ms.\n"

            if(res.result == 'ok') {
                res << ftp("productads.amazon-digital-ftp.com", "M_COMPUVESTC_639833", "M3b6ISPVyc", "amazon.txt", "compuvest.txt")

                contnt << "Upload file: ${res.time}ms.\nftp answer: ${res.message}\n"

                sendEmail("Make Amazon File ${new Date()}", contnt as String)
            }
        }
        return contnt
    }

    def static SELECT_AMAZON = """
        SELECT ad.mfrName mfrName,it.name itemName,ta.mfrNumber mfrNumber,it.accwareCatID accwareCode,it.weight weight,it.stock_quantity qty,
        ta.conditions conds,ad.upc upc,it.sell_price sellPrice
        FROM advert.tempItemsGroupForAdvert ta
        LEFT JOIN sklad.items it USING(item_id)
        LEFT JOIN sklad.itemsAdvert ad USING(item_id)
        WHERE it.sell_price > 100 AND it.category_id=? AND stock_quantity<>0
        AND it.vendor_id IN (?) """

    def makeAmazon() {

        def start = System.currentTimeMillis()
        def res = new StringBuilder()
        def count = 0

        def vendorTable = getVendorTable('2') // amazon=2
        if(vendorTable.isEmpty()) return [result: 'err', message: 'not Amazon vendor find']

        //def zip = new ZipOutputStream(new FileOutputStream("${PATH}/amazon.zip"))
        //zip.putNextEntry(new ZipEntry("amazon.txt"))

        def of = new File("${PATH}/amazon.txt").newWriter(false)
        of.writeLine("Category\tTitle\tLink\tSKU\tPrice\tImage")

//        zip << " Manufacturer\tDescription\tImage\tQauntity on Hand\tCondition\tShipping Weight\tShipping Cost\tUPC\tMfr part number\r\n"

        vendorTable.each { subcatid, vendors ->

            sql.eachRow(SELECT_AMAZON, [subcatid, vendors])
            { row ->
                count++

                // write to file
                //zip << buff
                of.writeLine(
                    "Electronics Accessories and Supplies\t" +
                    "${row.itemName}\t" +
                    "${WEB_LINK}${row.mfrNumber}&advsite=amazon&sku=${row.accwareCode}\t" +
                    "${row.accwareCode}\t" +
                    "${row.sellPrice}\t" +
                    "${IMG_URL}/${row.accwareCode}.jpg"
                )

//                buff << "${row.mfrName}\t"
//                buff << "${row.qty}\t"
//                buff << "${conds}\t"
//                buff << "${row.weight}\t"
//                buff << "9.98\t"
//                buff << "${row.upc}\t"
//                buff << "${row.mfrNumber}\r\n"
            }
            sql.close()
        }
        //zip.closeEntry()
        //zip.close()
        of.close()

        return [result: 'ok', time: System.currentTimeMillis() - start, count: count]
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="doPriceGrabber()">
    def doPriceGrabber() {

        def res = [:]
        def contnt = new StringBuilder("-----------PriceGrabber----------\n")

        res << makePriceGrabber()
        if(res.result == 'ok') {
            contnt << "Make file: items: ${res.count} ${res.time}ms.\n"

            if(res.result == 'ok') {

                sendEmail("Make PriceGrabber File ${new Date()}", contnt as String)
            }
        }
        return contnt
    }

    def static SELECT_PRICEGRABBER = """
        SELECT it.item_id itemID,it.accwareCatID sku,ta.mfrNumber mfrNumber,ad.upc upc,ad.mfrName mfrName,it.name itemName,
        LEFT(RIGHT(it.accwareCatID,2),2) accwareCode,ta.conditions conditions,it.sell_price sellPrice,it.weight weight,IF(cad.grabber='','Generic',cad.grabber) catName
        FROM advert.tempItemsGroupForAdvert ta
        LEFT JOIN sklad.items it USING(item_id)
        LEFT JOIN sklad.itemsAdvert ad USING(item_id)
        LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
        WHERE it.category_id=? AND it.sell_price > 20 AND it.vendor_id IN (?) """

    def makePriceGrabber() {

        def start = System.currentTimeMillis()
        def res = new StringBuilder()
        def count = 0

        def vendorTable = getVendorTable('7') // pricegrabber=7
        if(vendorTable.isEmpty()) return [result: 'err', message: 'not PriceGrabber vendor find']

        def of = new File("${PATH}/pricegrabber.txt").newWriter(false)
        of.writeLine("MPN,UPC,Manufacturer,Product Name,Image URLs,Referring Product URL,Product Condition,Availability,Selling Price,Shipping costs,Weight,SKU,Grabber categories")

        vendorTable.each { subcatid, vendors ->

            sql.eachRow(SELECT_PRICEGRABBER, [subcatid, vendors])
            { row ->
                count++

                def condition = 'OEM'
                switch(row.accwareCode as Integer) {
                    case 0:
                    case 1:
                    case 5: condition = 'New'; break;
                    case 3:
                    case 9:
                    case 59: condition = 'Open Box'; break;
                    case 10:
                    case 11:
                    case 12:
                    case 13:
                    case 31:
                    case 32: condition = 'Refurbished'; break;
                    case 22:
                    case 41:
                    case 42:
                    case 48: condition = 'Used'; break;
                    case 71:
                    case 72:
                    case 81:
                    case 82:
                    case 91:
                    case 92: condition = 'Like New'; break;
                    default: condition = 'OEM';
                }

                // write to file
                of.writeLine(
                    "${row.mfrNumber}," +
                    "," +
                    "${row.mfrName}," +
                    "${row.itemName}," +
                    "${IMG_URL}/${row.sku}.jpg," +
                    "${WEB_LINK}${row.mfrNumber}&advsite=grabber&sku=${row.sku}," +
                    "${condition}," +
                    "Yes," +
                    "${row.sellPrice}," +
                    "${8d + row.weight * 0.5}," +
                    "${row.weight}," +
                    "${row.sku}," +
                    "${row.catName}"
                )
            }
            sql.close()
        }
        of.close()

        return [result: 'ok', time: System.currentTimeMillis() - start, count: count]
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="doEpy()">
    def doEpy() {

        def res = [:]
        def contnt = new StringBuilder("-----------Epy----------\n")

        res << makeEpy()
        if(res.result == 'ok') {
            contnt << "Make file: items: ${res.count} ${res.time}ms.\n"

            if(res.result == 'ok') {

                sendEmail("Make Epy File ${new Date()}", contnt as String)
            }
        }
        return contnt
    }

    def static SELECT_EPY = """
        SELECT grp.name grpName,itvnd.alias als,itvnd.vendor_id vendID,
        grp.defQty0_30 defQty0_30,grp.minQty0_30 minQty0_30,grp.margin0_30 margin0_30,grp.charge0_30 charge0_30,
        grp.defQty31_60 defQty31_60,grp.minQty31_60 minQty31_60,grp.margin31_60 margin31_60,grp.charge31_60 charge31_60,
        grp.defQty61_90 defQty61_90,grp.minQty61_90 minQty61_90,grp.margin61_90 margin61_90,grp.charge61_90 charge61_90,
        grp.defQty91_180 defQty91_180,grp.minQty91_180 minQty91_180,grp.margin91_180 margin91_180,grp.charge91_180 charge91_180,
        grp.defQty181_240 defQty181_240,grp.minQty181_240 minQty181_240,grp.margin181_240 margin181_240,grp.charge181_240 charge181_240,
        grp.defQty241_more defQty241_more,grp.minQty241_more minQty241_more,grp.margin241_more margin241_more,grp.charge241_more charge241_more,
        itm.accwareCatID sku,itm.sell_price sellPrice,itm.hot_price hotPrice,IF(itm.stock='Y',1,0) stock,itm.stock_quantity qty,itm.weight weight,

        IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 0 AND 30,1) old0_30,
        IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 31 AND 60,0) old31_60,
        IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 61 AND 90,0) old61_90,
        IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 91 AND 180,0) old91_180,
        IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 181 AND 240,0) old181_240,
        IFNULL(DATEDIFF(NOW(),itm.stockDate) >= 241,0) old241_more,

        vend.amazonMargin amazonMargin,vend.amazonCharge amazonCharge,vend.amazonQty amazonQty,
        vend.amzcompMargin amzcompMargin,vend.amzcompCharge amzcompCharge,vend.amzcompQty amzcompQty,
        vend.ebayMargin ebayMargin,vend.ebayCharge ebayCharge,vend.ebayQty ebayQty,
        vend.buycomMargin buycomMargin,vend.buycomCharge buycomCharge,vend.buycomQty buycomQty,
        vendcat.amazonSend amazonSend,vendcat.amzcompSend amzcompSend,vendcat.ebaySend ebaySend,vendcat.buycomSend buycomSend

        FROM advert.epyGroups grp
        LEFT JOIN advert.epyGroupSubCategory grpcat USING(group_id)
        LEFT JOIN sklad.items itm USING(category_id)
        LEFT JOIN sklad.itemVendor itvnd USING(vendor_id)
        LEFT JOIN advert.epyVendors vend USING(vendor_id)
        LEFT JOIN advert.epyVendorSubCategories vendcat ON(vendcat.vendor_id=itm.vendor_id AND vendcat.category_id=itm.category_id)
        WHERE itm.active='Y' AND grp.active AND vend.active=1 """

    def static HEADER = "Group,SKU,Vendor Alias,Qty,Price,Weight"

    def makeEpy() {

        def start = System.currentTimeMillis()
        def amazonVendors = [:], amzcompVendors = [:], ebayVendors = [:], buycomVendors = [:]

        def count  = 0,  amazonCount = 0,  amzcompCount = 0,  ebayCount   = 0,  buycomCount  = 0,  vendorID  = 0
        def defQty = 0,  minQty      = 0,  qty          = 0,  amazonQty   = 0,  amzcompQty   = 0,  ebayQty   = 0,  buycomQty   = 0
        def margin = 0d, charge      = 0d, price        = 0d, amazonPrice = 0d, amzcompPrice = 0d, ebayPrice = 0d, buycomPrice = 0d

        def amazon = new File("${PATH}/epy/amazon.txt").newWriter(false)
        def amzcomp = new File("${PATH}/epy/amzcomp.txt").newWriter(false)
        def ebay = new File("${PATH}/epy/ebay.txt").newWriter(false)
        def buycom = new File("${PATH}/epy/buycom.txt").newWriter(false)
        amazon.writeLine(HEADER)
        amzcomp.writeLine(HEADER)
        ebay.writeLine(HEADER)
        buycom.writeLine(HEADER)

        def notIn = "";
        sql.eachRow("SELECT GROUP_CONCAT(item_id) itemids FROM advert.epyUserItems WHERE send=1")
        { row ->
            notIn = " AND itm.item_id NOT IN (${row.itemids})"
        }
        sql.close()

        sql.eachRow(SELECT_EPY + notIn)
        { row ->

            defQty = row.defQty0_30
            minQty = row.minQty0_30
            margin = row.margin0_30
            charge = row.charge0_30
            price  = row.sellPrice
            vendorID = row.vendID

            if(vendorID == 2) { // Compuvest
                if(row.old0_30) {
                }
                else if(row.old31_60) {
                    defQty = row.defQty31_60
                    minQty = row.minQty31_60
                    margin = row.margin31_60
                    charge = row.charge31_60
                }
                else if(row.old61_90) {
                    defQty = row.defQty61_90
                    minQty = row.minQty61_90
                    margin = row.margin61_90
                    charge = row.charge61_90
                    price = row.hotPrice
                }
                else if(row.old91_180) {
                    defQty = row.defQty91_180
                    minQty = row.minQty91_180
                    margin = row.margin91_180
                    charge = row.charge91_180
                    price = row.hotPrice
                }
                else if(row.old181_240) {
                    defQty = row.defQty181_240
                    minQty = row.minQty181_240
                    margin = row.margin181_240
                    charge = row.charge181_240
                    price = row.hotPrice
                }
                else if(row.old241_more) {
                    defQty = row.defQty241_more
                    minQty = row.minQty241_more
                    margin = row.margin241_more
                    charge = row.charge241_more
                    price = row.hotPrice
                }
            }

            // group
            price = (price / 100d * margin) + price + charge;
            qty = row.stock ? defQty : row.qty - minQty;

            // vendor
            amazonPrice = (price / 100d * row.amazonMargin) + price + row.amazonCharge
            amazonQty = row.stock ? qty : qty - row.amazonQty

            amzcompPrice = (price / 100d * row.amzcompMargin) + price + row.amzcompCharge
            amzcompQty = row.stock ? qty : qty - row.amzcompQty

            ebayPrice = (price / 100d * row.ebayMargin) + price + row.ebayCharge
            ebayQty = row.stock ? qty : qty - row.ebayQty

            buycomPrice = (price / 100d * row.buycomMargin) + price + row.buycomCharge
            buycomQty = row.stock ? qty : qty - row.buycomQty

            // files
            if(row.amazonSend && amazonQty > 0 && amazonPrice > 0d) {
                amazon.writeLine(
                    "," +
                    "${row.grpName}," +
                    "${row.sku}," +
                    "${row.als}," +
                    "${amazonQty}," +
                    "${amazonPrice}," +
                    "${row.weight}"
                )

                // count vendor items send to advert
                count = getCount(vendorID, amazonVendors);
                amazonVendors.put(vendorID, count);
            }
            if(row.amzcompSend && amzcompQty > 0 && amzcompPrice > 0d) {
                amzcomp.writeLine(
                    "," +
                    "${row.grpName}," +
                    "${row.sku}," +
                    "${row.als}," +
                    "${amzcompQty}," +
                    "${amzcompPrice}," +
                    "${row.weight}")

                // count vendor items send to advert
                count = getCount(vendorID, amzcompVendors);
                amzcompVendors.put(vendorID, count);
           }
           if(row.ebaySend && ebayQty > 0 && ebayPrice > 0d) {
                ebay.writeLine(
                    "," +
                    "${row.grpName}," +
                    "${row.sku}," +
                    "${row.als}," +
                    "${ebayQty}," +
                    "${ebayPrice}," +
                    "${row.weight}")

               // count vendor items send to advert
               count = getCount(vendorID, ebayVendors);
               ebayVendors.put(vendorID, count);
           }
           if(row.buycomSend && buycomQty > 0 && buycomPrice > 0d) {
                buycom.writeLine(
                    "," +
                    "${row.grpName}," +
                    "${row.sku}," +
                    "${row.als}," +
                    "${buycomQty}," +
                    "${buycomPrice}," +
                    "${row.weight}")

               // count vendor items send to advert
               count = getCount(vendorID, buycomVendors);
               buycomVendors.put(vendorID, count);
           }
        }
        sql.close()

            // store to file special items from epyUserItems
        sql.eachRow(
            "SELECT itm.accwareCatID sku,itvnd.alias als, itvnd.vendor_id vendID,itm.weight weight," +
            "eui.amazonPrice amazonPrice,eui.amazonQty amazonQty," +
            "eui.amzcompPrice amzcompPrice,eui.amzcompQty amzcompQty," +
            "eui.ebayQty ebayQty,eui.ebayPrice ebayPrice," +
            "eui.buycomQty buycomQty,eui.buycomPrice buycomPrice " +
            "FROM advert.epyUserItems eui " +
            "LEFT JOIN sklad.items itm USING(item_id) " +
            "LEFT JOIN sklad.itemVendor itvnd USING(vendor_id) " +
            "WHERE itm.active='Y' AND eui.send=1")
        { row ->

            amazon.writeLine(
                "," +
                "Compuvest Special," +
                "${row.sku}," +
                "${row.als}," +
                "${row.amazonQty}," +
                "${row.amazonPrice}," +
                "${row.weight}")

            amzcomp.writeLine(
                "," +
                "Compuvest Special," +
                "${row.sku}," +
                "${row.als}," +
                "${row.amzcompQty}," +
                "${row.amzcompPrice}," +
                "${row.weight}")

            ebay.writeLine(
                "," +
                "Compuvest Special," +
                "${row.sku}," +
                "${row.als}," +
                "${row.ebayQty}," +
                "${row.ebayPrice}," +
                "${row.weight}")

            buycom.writeLine(
                "," +
                "Compuvest Special," +
                "${row.sku}," +
                "${row.als}," +
                "${row.buycomQty}," +
                "${row.buycomPrice}," +
                "${row.weight}")

            // count vendor items send to advert
            count = getCount(vendorID, amazonVendors);
            amazonVendors.put(vendorID, count);

            count = getCount(vendorID, amzcompVendors);
            amzcompVendors.put(vendorID, count);

            count = getCount(vendorID, ebayVendors);
            ebayVendors.put(vendorID, count);

            count = getCount(vendorID, buycomVendors);
            buycomVendors.put(vendorID, count);
        }
        sql.close()

        /////////////////////////////
        // for amazon
        sql.withBatch(amazonVendors.size(), "UPDATE advert.epyVendors SET amazonSendQty=? WHERE vendor_id=?") { row ->
            amazonVendors.each { vendor ->
                amazonCount += amazonVendors.get(vendor);

                row.addBatch(amazonVendors.get(vendor), vendor)
            }
        }
        sql.close()

        // for amzcomp
        sql.withBatch(amzcompVendors.size(), "UPDATE advert.epyVendors SET amzcompSendQty=? WHERE vendor_id=?") { row ->
            amzcompVendors.each { vendor ->
                amzcompCount += amzcompVendors.get(vendor);

                row.addBatch(amzcompVendors.get(vendor), vendor)
            }
        }
        sql.close()

        // for ebay
        sql.withBatch(ebayVendors.size(), "UPDATE advert.epyVendors SET ebaySendQty=? WHERE vendor_id=?") { row ->
            ebayVendors.each { vendor ->
                ebayCount += ebayVendors.get(vendor);

                row.addBatch(ebayVendors.get(vendor), vendor)
            }
        }
        sql.close()

        // for buycom
        sql.withBatch(buycomVendors.size(), "UPDATE advert.epyVendors SET buycomSendQty=? WHERE vendor_id=?") { row ->
            buycomVendors.each { vendor ->
                buycomCount += buycomVendors.get(vendor);

                row.addBatch(buycomVendors.get(vendor), vendor)
            }
        }
        sql.close()

        // set status
        time = System.currentTimeMillis() - time;
        sql.executeUpdate("UPDATE advert.stat SET cntAmazon=?,cntAmzComp=?,cntEbay=?,cntBuycom=?,time=?,build=NOW() WHERE name='Epy'",
            [amazonCount, amzcompCount, ebayCount, buycomCount, time as Integer])
        sql.close()

        // close files
        amazon.close();
        amzcomp.close();
        ebay.close();
        buycom.close();

        return [result: 'ok', time: System.currentTimeMillis() - start, count: count]
    }
// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="ftp()">

    def ftp(serverName, username, password, inputFileName, remoteFileName) {

        def start = System.currentTimeMillis()

        def res = new StringBuilder()
        new FTPClient().with {

            connect serverName
            res << '\t' << replyString << '\n'

            enterLocalPassiveMode()
            res << '\t' << replyString << '\n'

            login username, password
            res << '\t' << replyString << '\n'

            changeWorkingDirectory '.'

            fileType = FTPClient.BINARY_FILE_TYPE
            res << '\t' << replyString << '\n'

            // upload file
            def file = new File("${PATH}/${inputFileName}")
            file.withInputStream { fis ->
                storeFile remoteFileName, fis
            }
            res << '\t' << replyString << '\n'

            // download file
            //def file = new File("${PATH}/${inputFileName}")
            //file.withOutputStream { ostream ->
            //    retrieveFile remoteFileName, ostream
            //}

            disconnect()
        }
        return [result: 'ok', time: System.currentTimeMillis() - start, message: res]
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendEmail()">

    def sendEmail(subject, body) {

        gmail.send(
            "webserver@compuvest.com",
            ["dima@compuvest.com"],
            subject,
            body,
            "text/plain"
        )
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="initTempItemsTable()">

    def final static MAKE_TABLE_SORT = """
	CREATE TABLE IF NOT EXISTS advert.tempItemsSortForAdvert (
	item_id int(10) unsigned NOT NULL default '0',
	mfrNumber varchar(30) NOT NULL default '',
	conditions int(1) unsigned NOT NULL default '0',
	vendorID tinyint(3) NOT NULL default '2',
	KEY idx_item_id(item_id),
	KEY idx_mfrNumber(mfrNumber),
	KEY idx_conditions(conditions),
	KEY idx_vendorID(vendorID)
	) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='sort by sell_price,conditions temporary table for advert' """

    def final static MAKE_TABLE_GROUP = """
	CREATE TABLE IF NOT EXISTS advert.tempItemsGroupForAdvert (
	item_id int(10) unsigned NOT NULL default '0',
	mfrNumber varchar(30) NOT NULL default '',
	conditions varchar(20) NOT NULL default '',
	KEY idx_item_id(item_id)
	) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='finish temporary table for advert' """

    def final static INIT_ITEMS_SORT_TABLE = """
	INSERT INTO advert.tempItemsSortForAdvert
        (SELECT it.item_id item_id,ad.mfrNumber mfrNumber,LEFT(RIGHT(it.accwareCatID,2),1) conditions,it.vendor_id vendorID
        FROM sklad.items it
	LEFT JOIN sklad.itemsAdvert ad USING(item_id)
        WHERE it.active='Y' AND ad.mfrNumber<>''
	ORDER BY it.sell_price) """

    def final static INIT_ITEMS_GROUP_TABLE = """
	INSERT INTO advert.tempItemsGroupForAdvert
        (SELECT isa.item_id item_id,isa.mfrNumber mfrNumber,cs.description conditions
        FROM advert.tempItemsSortForAdvert isa
        LEFT JOIN sklad.conditions cs ON isa.conditions=cs.id
        GROUP BY isa.mfrNumber,isa.conditions) """

    def initTempTables() {

        def start = System.currentTimeMillis()

        sql.executeUpdate(MAKE_TABLE_SORT)
        sql.close()

        sql.executeUpdate("TRUNCATE advert.tempItemsSortForAdvert")
        sql.close()

        sql.executeUpdate(MAKE_TABLE_GROUP)
        sql.close()

        sql.executeUpdate("TRUNCATE advert.tempItemsGroupForAdvert")
        sql.close()

        sql.executeUpdate(INIT_ITEMS_SORT_TABLE)
        sql.close()

        sql.executeUpdate(INIT_ITEMS_GROUP_TABLE)
        sql.close()

        return  "Init temp table: ${System.currentTimeMillis() - start}ms.\n"
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVendorTable()">
/*
	SELECT subcat_id,CAST(GROUP_CONCAT(vendor_id) AS CHAR) vendors
	FROM advert.advertSend where advert_id=2
        GROUP BY subcat_id;
*/
    def final static String GET_VENDOR = """
	SELECT subcat_id,CAST(GROUP_CONCAT(vendor_id) AS CHAR) vendors
	FROM advert.advertSend where advert_id=?
        GROUP BY subcat_id """

    def getVendorTable(advertid) {

        def vendorTable = [:]
        sql.eachRow(GET_VENDOR, [advertid])
        { row ->
            vendorTable[row.subcat_id] = row.vendors
        }
        sql.close()

        return vendorTable;
    }
// </editor-fold>

    def void doGet(HttpServletRequest request, HttpServletResponse response) {
        processRequest(request, response)
    }
    def void doPost(HttpServletRequest request, HttpServletResponse response) {
        processRequest(request, response)
    }
    String getServletInfo() {
        return "MakeFile for advert"
    }
    def void destroy() {
    }
}
